import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings("ignore")
import matplotlib.pyplot as plt
%matplotlib inline
plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签
plt.rcParams['axes.unicode_minus']=False #用来正常显示负号
import warnings
warnings.filterwarnings("ignore")
import plotly.express as px
from syx_mapping import col_name_dict
import plotly.offline as py

df=pd.read_csv(r"C:\Users\DELL\Desktop\融资情报局\融资情报局9月数据.csv")

df["money"]=df["money"]/100
df["credit_line"]=np.where(df["credit_line"]>df["money"],df["credit_line"],df["money"])
df=df.drop("money",axis=1)

df["product_name"]=df["product_name"].map(lambda x:x.replace('（1）','').replace('（2）',''))

df["label"]=np.where(df["credit_line"]>0,1,0)
df9=df[(df["busi_time"]>='2023-09-01') & (df["busi_time"]<'2023-10-01')]
df10=df[(df["busi_time"]>='2023-10-01') & (df["busi_time"]<'2023-11-01')]

df["month"]=pd.to_datetime(df["busi_time"]).dt.month.astype(str)+"月"
df10_11=df10.groupby('product_name').agg({"product_id":"count","credit_line":"sum"}).sort_values(by=["credit_line"],ascending=False)[:10].reset_index()


df10_11["avg_credit_line"]=(df10_11["credit_line"]/df10_11["product_id"]/10000).astype(int)
df10_11=df10_11.sort_values(by="avg_credit_line",ascending=False)
df_ratio=((df10.groupby('product_name').agg({"credit_line":"mean"})-df9.groupby('product_name').agg({"credit_line":"mean"}))/df9.groupby('product_name').agg({"credit_line":"mean"})).sort_values(by="credit_line",ascending=False)


df_ratio["8月平均放款额"]=df9.groupby('product_name').agg({"credit_line":"mean"})
df_ratio["9月平均放款额"]=df10.groupby('product_name').agg({"credit_line":"mean"})


df_ratio=df_ratio[df_ratio.index.map(lambda x:'个人' not in x and '平安银行' not in x and '新网银行' not in x and '新希望' not in x and '瀚华金融' not in x)][:10]

df_ratio=df_ratio[:10]


df_ratio=df_ratio.reset_index()
df_ratio["credit_line"]=df_ratio["credit_line"]*10



fig=px.line(df_ratio,x="product_name",y="credit_line",title="10月份相比9月份平均授信额度增长率")
fig.update_traces(hovertemplate="平均授信额度增长率:%{y:.1f}%")
fig.show()


df_syx=pd.concat([df10[df10["product_name"]=="富民银行-好采贷"],df9[df9["product_name"]=="富民银行-好采贷"]])
df_syx["reg_year"]=(pd.datetime.today() - pd.to_datetime(df_syx.reg_date)).dt.days/365.25
df_syx['legal_rep_change_date']=(pd.datetime.today() - pd.to_datetime(df_syx.legal_rep_change_date)).dt.days/365.25




df_syx.columns=df_syx.columns.map(lambda x:col_name_dict.get(x,x))
columns=[col_name_dict.get(col) for col in col_name_dict.keys() if col not in ('eid','tax_code','company_name','idx','open_status_tag','legal_person','reg_cap_str','tel','more_tel','email','more_email','social_credit_code','reg_code','org_code','industry_code','pass_name','web_site','town','addr','new_addr','scope','reg_date','check_date','gd_lng','gd_lat','province_code','city_code','town_code','industry_main_code','company_type_code','create_time','update_time','white_list','has_mobile','has_line_phone','has_email','annual_report_list')]


for name,dtype in df_syx[columns].dtypes.reset_index().values:
    if name in ('实缴资本（数值）','行政处罚次数','疑似代记账电话','贷款意愿分层标签','贷款额度预测分层标签','贷款通过率预测分层标签','开庭公告数量','裁判文书数量','立案数量','法人持股比例','实缴资本（文本）','资产总额','利润总额','净利润','税收总额','所有者权益总额','被执行人次数'):continue
    #print(name)
    results=[]
    if str(dtype)=="object":
        values=df_syx[name].drop_duplicates()
        for value in values:
            results.append([str(value)[:50],df_syx[(df_syx[name].map(lambda x:str(x) in str(value))) ]["credit_line"].mean()])
    else:
        values=[-1e10]
        for i in range(1,10):
            value=df_syx[name].quantile(i/10)
            if value not in values:
                values.append(value)
        values.append(1e10)
        #print(values)
        if len(values)<=3:continue
        for i in range(len(values)-1):
            results.append(["大于等于%s,小于%s"%(str(round(values[i],2)),str(round(values[i+1],2))),df_syx[(df_syx[name]>=values[i]) &(df_syx[name]<values[i+1])]["credit_line"].mean()])
    data=pd.DataFrame(results).rename(columns={0:name,1:"平均授信额度"}).sort_values(by="平均授信额度",ascending=False)
    data=pd.concat([data.iloc[:10],data.iloc[-10:]]).drop_duplicates()
    data["平均授信额度"]=data["平均授信额度"]/10000
    if len(data)==20:
        print(name,"偏好:%s;厌恶:%s"%(",".join(data[name].iloc[:10]),",".join(data[name].iloc[-10:])))
    else:
        print(name)
    fig = px.bar(data, x=name, y='平均授信额度',color=name,title="富民银行-好采贷%s不同取值的平均授信额度"%name,)
    fig.update_traces(hovertemplate="授信额度:%{y:.0f}万")
    #fig.show()
    ##HTML(fig.to_html())
    py.iplot(fig)



